USE [DS2012]
GO

IF object_id('usp_DislocationLoaderFinish') is not null
	DROP PROCEDURE usp_DislocationLoaderFinish
GO

CREATE PROCEDURE usp_DislocationLoaderFinish 
AS SET NOCOUNT ON
BEGIN
	INSERT INTO Stations (Code)
		SELECT DISTINCT tmpD.ArrivalStation FROM tmpDislocation tmpD
		LEFT JOIN Stations St ON tmpD.ArrivalStation = St.Code
		WHERE St.ID IS NULL
		
	INSERT INTO Stations (Code)
		SELECT DISTINCT tmpD.DepartureStation FROM tmpDislocation tmpD
		LEFT JOIN Stations St ON tmpD.DepartureStation = St.Code 
		WHERE St.ID IS NULL
		
	INSERT INTO Stations (Code)
		SELECT DISTINCT tmpD.StationOfOperation FROM tmpDislocation tmpD
		LEFT JOIN Stations St ON tmpD.StationOfOperation = St.Code 
		WHERE St.ID IS NULL
		
	INSERT INTO CarTypes (Code)
		SELECT DISTINCT tmpD.TypeOfConditionalCodeOfCar FROM tmpDislocation tmpD
		LEFT JOIN CarTypes CT ON tmpD.TypeOfConditionalCodeOfCar = CT.Code 
		WHERE CT.ID IS NULL
	
	INSERT INTO Cargo (Code)
		SELECT DISTINCT tmpD.CargoRateCode FROM tmpDislocation tmpD
		LEFT JOIN Cargo ON tmpD.CargoRateCode = Cargo.Code 
		WHERE Cargo.ID IS NULL
		
	INSERT INTO Cargo (Code)
		SELECT DISTINCT tmpD.CargoOperativeCode FROM tmpDislocation tmpD
		LEFT JOIN Cargo ON tmpD.CargoOperativeCode = Cargo.Code 
		WHERE Cargo.ID IS NULL
		
	INSERT INTO Recipients (Code)
		SELECT DISTINCT tmpD.RecipientCode FROM tmpDislocation tmpD
		LEFT JOIN Recipients ON tmpD.RecipientCode = Recipients.Code 
		WHERE Recipients.ID IS NULL
		
	INSERT INTO Operations (Code)
		SELECT DISTINCT tmpD.OperationCode FROM tmpDislocation tmpD
		LEFT JOIN Operations ON tmpD.OperationCode = Operations.Code 
		WHERE Operations.ID IS NULL
		
	INSERT INTO Roads (Code)
		SELECT DISTINCT tmpD.DeliveryRoad FROM tmpDislocation tmpD
		LEFT JOIN Roads ON tmpD.DeliveryRoad = Roads.Code 
		WHERE Roads.ID IS NULL
		
	INSERT INTO Roads (Code)
		SELECT DISTINCT tmpD.ReceptRoad FROM tmpDislocation tmpD
		LEFT JOIN Roads ON tmpD.ReceptRoad = Roads.Code 
		WHERE Roads.ID IS NULL
			
	UPDATE tmpDislocation
		SET tmpDislocation.ArrivalStation = (SELECT St.ID FROM Stations St
												WHERE St.Code = tmpDislocation.ArrivalStation),
			tmpDislocation.DepartureStation = (SELECT St.ID FROM Stations St
													WHERE St.Code = tmpDislocation.DepartureStation),
			tmpDislocation.StationOfOperation = (SELECT St.ID FROM Stations St
													WHERE St.Code = tmpDislocation.StationOfOperation),
			tmpDislocation.TypeOfConditionalCodeOfCar = (SELECT CR.ID FROM CarTypes CR
															WHERE CR.Code = tmpDislocation.TypeOfConditionalCodeOfCar),
			tmpDislocation.CargoRateCode = (SELECT Cargo.ID FROM Cargo 
												WHERE Cargo.Code = tmpDislocation.CargoRateCode),
			tmpDislocation.CargoOperativeCode = (SELECT Cargo.ID FROM Cargo
													WHERE Cargo.Code = tmpDislocation.CargoOperativeCode),
			tmpDislocation.RecipientCode = (SELECT Rsp.ID FROM Recipients Rsp
												WHERE Rsp.Code = tmpDislocation.RecipientCode),
			tmpDislocation.OperationCode = (SELECT Operations.ID FROM Operations
													WHERE Operations.Code = tmpDislocation.OperationCode),
			tmpDislocation.DeliveryRoad = (SELECT Roads.ID FROM Roads
													WHERE Roads.Code = tmpDislocation.DeliveryRoad),
			tmpDislocation.ReceptRoad = (SELECT Roads.ID FROM Roads 
													WHERE Roads.Code = tmpDislocation.ReceptRoad)
												
												
	
	INSERT INTO Dislocation 
		SELECT CarNumber,RollCheck,Lightweight,KindOfCar,TypeOfConditionalCodeOfCar,
			StartDate,FeatTracking,StateCode,ActualCargoWeight,ArrivalStation,CargoRateCode,CargoOperativeCode,
			RecipientCode,SpecialNotesOfCar,NumberOfCaseCG,NumberOfCaseCP,NumberOfCaseKG,NumberOfCaseKP,
			ListNote,CarParkType,FaultCode,DepartureStation,OperationCode,OperationDate,StationOfOperation,
			DeliveryRoad,ReceptRoad,TrainIndex,TrainNumber
			 FROM tmpDislocation

	DELETE FROM tmpDislocation
END